sql计算上班总时长

您所在的位置：网站首页 › 刷卡打卡机第一次打卡上班第二次打卡变记录 › sql计算上班总时长

sql计算上班总时长

2024-07-07 16:53| 来源: 网络整理| 查看: 265

本文交代了一种求上班总时长的特殊的业务场景，并造了一个玩具数据，分别用MySQL和Hive SQL给出了其计算逻辑。

一、业务背景及口径说明

指标上班总时长 = SUM(下班时间-上班时间) ，但在特殊的业务场景下并没有那么容易得到，如：

某员工一天的操作为：1→1→1→2→2（上班打卡为1，下班打卡为2）。因为可以重复打卡，该员工打完卡后不确定是否打卡成功，又进行多次操作。

此时，定义指标上班总时长口径：

员工每天第一次的上班打卡时间为上班时间，第一次下班打卡时间为下班时间（即 1→1→1→2→2 取加粗的情况）；上班总时长 = SUM(下班时间-上班时间)。

对于这个口径，该怎么写sql的计算逻辑呢？

二、计算逻辑 2.1 MySQL

MySQL 8.0之前的版本不支持窗口函数，此处不使用窗口函数。

在此，先造一个玩具数据用于说明：

-- 直接粘贴进查询语句里执行即可，会创建一个test数据库及test数据库里的work_time1表 -- create test database create database if not exists test character set utf8; -- DDL drop table if exists test.work_time1; create table test.work_time1 ( id int not null auto_increment comment '主键，表示记录数' , user_id int not null comment '用户编号' , user_type int not null comment '用户类型：1表示上班，2表示下班' , create_time datetime not null comment '创建时间' , primary key(id) ) engine=innodb default charset=utf8 ; -- insert data insert into test.work_time1( user_id , user_type , create_time ) values(101, 1, '2020-01-05 09:00:00') , (101, 1, '2020-01-05 09:05:30') , (102, 1, '2020-01-05 09:15:02') , (101, 2, '2020-01-05 18:06:31') , (102, 2, '2020-01-05 18:30:31') , (102, 2, '2020-01-05 18:31:21') , (101, 1, '2020-01-06 08:45:22') , (101, 2, '2020-01-06 18:07:28') , (101, 1, '2020-01-07 09:30:02') , (101, 2, '2020-01-07 18:10:30') , (101, 2, '2020-01-07 18:15:21') ;

表的结果如下所示：

iduser_iduser_typecreate_time110112020-01-05 09:00:00210112020-01-05 09:05:30310212020-01-05 09:15:02410122020-01-05 18:06:31510222020-01-05 18:30:31610222020-01-05 18:31:21710112020-01-06 08:45:22810122020-01-06 18:07:28910112020-01-07 09:30:021010122020-01-07 18:10:301110122020-01-07 18:15:21

其中：

id: 主键，仅表示记录数；user_id: 员工id；user_type: 员工操作类型，1表示上班，2表示下班；create_time: 创建时间。

由此可知，上班时间的计算逻辑（if user_type = 1 then create_time），下班时间的计算逻辑（if user_type = 2 then create_time）。因为数据较少，可以先手工计算，最后与sql输出的结果做对比。只保留每天第一次的上下班记录的话，最后保留的记录只有1、3、4、5、7、8、9、10这10条记录。

-- 员工101的上班总时长：27.1514小时 select ((unix_timestamp('2020-01-05 18:06:31') - unix_timestamp('2020-01-05 09:00:00')) + (unix_timestamp('2020-01-06 18:07:28') - unix_timestamp('2020-01-06 08:45:22')) + (unix_timestamp('2020-01-07 18:10:30') - unix_timestamp('2020-01-07 09:30:02'))) / 3600 -- 员工102的上班总时长：9.2581小时 select (unix_timestamp('2020-01-05 18:30:31') - unix_timestamp('2020-01-05 09:15:02')) / 3600

如上所示，手工计算最后员工101的上班总时长为27.1514小时，员工102的上班总时长为9.2581小时。接下来用MySQL写计算逻辑：

step 1: 数据清洗，仅保留需要的记录可以将该问题理解为求组内 TopN 问题，在这个场景下就是求每个员工在每天每个打卡行为（上班或下班）时的最早的时间：思路1：因为是 Top1 问题，只要满足在组（user_id, date(create_time), user_type）中，create_time取最小值即可。

select id , user_id , user_type , create_time from test.work_time1 where (user_id, date(create_time), user_type, create_time) in ( select user_id, date(create_time), user_type, min(create_time) from test.work_time1 group by user_id, date(create_time), user_type ) ;

思路2：可以将组内最早的时间理解为：组内比该时间还早的时间数 < 1。

select id , a.user_id , a.user_type , a.create_time from test.work_time1 a where 1 > (select count(*) from test.work_time1 b where b.user_id = a.user_id and date(b.create_time) = date(a.create_time) and b.user_type = a.user_type and b.create_time (select count(*) from test.work_time1 b where b.user_id = a.user_id and date(b.create_time) = date(a.create_time) and b.user_type = a.user_type and b.create_time (select count(*) from test.work_time1 b where b.user_id = a.user_id and date(b.create_time) = date(a.create_time) and b.user_type = a.user_type and b.create_time

【本文地址】

sql计算上班总时长

sql计算上班总时长

今日新闻

推荐新闻